#include "widget.h"
#include "ui_widget.h"
#include<QSqlDatabase>
#include<QDebug>
#include<QSqlError>
#include<QMessageBox>
#include<QSqlQuery>//执行SQL语句的类
#include<QVariantList>

Widget::Widget(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Widget)
{
    ui->setupUi(this);

    //打印QT支持的数据库驱动
    qDebug() << QSqlDatabase::drivers();//"QSQLITE", "QMYSQL", "QMYSQL3", "QODBC", "QODBC3", "QPSQL", "QPSQL7"
    //添加QMYSQL数据库
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");//此时默认连接名 ,"connect_1"
    db.setHostName("localhost");//数据库用户名
    db.setUserName("root");
    db.setPassword("258369");//密码
    db.setDatabaseName("test");//使用哪个数据库，非代码创建

    /*
     * Sqlite数据库使用
     * QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
     * db.setDatabaseName("../info.db");//
     * Sqlite数据库不要使用create table student(id int primary key auto_increment, name varchar(255), age int, score int);
     * 因为auto_increment
    */

    //打开数据库
    if(!db.open())//数据库打开失败
    {
       QMessageBox::warning(this,"错误",db.lastError().text());
       return;
    }

    //QSqlQuery query(db);
    //创建表格，创建一次就好了
    //query.exec("create table fan(id int primary key auto_increment, name varchar(255), age int, score int);");
    //query.exec("insert into fan(id, name, age, score) values(2, 'lucy', 22, 90);");

    /*
    //批量插入  ODBC风格 预处理语句
    query.prepare("insert into fan(name, age, score) values(? , ? , ?);");
    //给字段设定内容 List
    QVariantList nameList;
    nameList << "xiaoming" << "xiaojiang" << "xiaohong";
    QVariantList ageList;
    ageList << 11 << 22 <<33;
    QVariantList scoreList;
    scoreList << 59 << 59 <<33;
    //给字段绑定相应值，按顺序绑定
    query.addBindValue(nameList);
    query.addBindValue(ageList);
    query.addBindValue(scoreList);
    //执行预处理命令
    query.execBatch();
  */

  //遍历
   QSqlQuery query;
   query.exec("select * from student;");
   while(query.next())//一行行遍历
   {
       //取出当前行的内容  以列为单位
       qDebug() << query.value(0).toInt()
                << query.value(1).toString()
                << query.value("age").toInt()   //另一种方式，字段名
                << query.value("score").toInt();
   }

}

Widget::~Widget()
{
    delete ui;
}

void Widget::on_buttonDel_clicked()
{
    //获取行编辑内容
    QString name = ui->lineEdit->text();
    QString sql = QString("delete from fan where name = '%1';").arg(name);

    //开启一个事务
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.exec(sql);
}

void Widget::on_buttonSure_clicked()
{
    //确定删除
    QSqlDatabase::database().commit();
}

void Widget::on_buttonCancel_clicked()
{
    //回滚 撤销
    QSqlDatabase::database().rollback();
}
